package jwdt.xadmin.finace.action;

import java.awt.Color;
import java.awt.Font;
import java.io.File;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletContext;

import jwdt.common.util.CacheUtil;

import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartUtilities;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.labels.ItemLabelAnchor;
import org.jfree.chart.labels.ItemLabelPosition;
import org.jfree.chart.labels.StandardCategoryItemLabelGenerator;
import org.jfree.chart.labels.StandardPieSectionLabelGenerator;
import org.jfree.chart.plot.CategoryPlot;
import org.jfree.chart.plot.PiePlot3D;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.chart.renderer.category.BarRenderer3D;
import org.jfree.data.category.CategoryDataset;
import org.jfree.data.general.DatasetUtilities;
import org.jfree.data.general.DefaultPieDataset;
import org.jfree.ui.TextAnchor;
import org.jfree.util.Rotation;

import com.ezerg.db.DataMap;
import com.ezerg.db.Jdb;
import com.ezerg.util.DateUtil;
import com.ezerg.util.MathUtil;
import com.ezerg.web.Form;
import com.ezerg.web.Module;
import com.ezerg.web.Page;
import com.ezerg.web.ServletActionContext;
import com.ezerg.web.annotation.Controller;
import com.ezerg.web.annotation.RequestMapping;

@Controller("/xadmin/finace")
@SuppressWarnings({"rawtypes", "unchecked"})
public class ReportAction extends FinaceAction {

	private File checkUploadPath() {
		ServletContext sc = ServletActionContext.getServletContext();
		String basePath = sc.getRealPath("/");
		String uploadPath = basePath.replace("\\", "/") + "uploadfile/chart/";
		File up = new File(uploadPath);
		try {
			if (!up.exists()) {
				up.mkdirs();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return up;
	}
	
 	@RequestMapping("/reportlist")
	public Page doReportlist(Form f, Module m) throws Exception {
		// 
		String sql = "SELECT SUBSTRING(logtime, 1, 7) as dt, SUM(a.logprice) AS tp FROM ha_log a, ha_flmx b WHERE a.type=b.MXDM";
		//
		int year = f.getInt("year2", Calendar.getInstance().get(Calendar.YEAR));
		String account = f.getString("account2");
		if (!"".equals(account)) {
			sql += " and account = '" + account + "'";
		}
		f.add("accounts", Jdb.get().query(super.sql_selAcc, CacheUtil.C0));
		String logtime2 = f.getString("logtime2");
		if (!"".equals(logtime2)) {
			sql += " and logtime >= '" + logtime2 + "'";
		}
		String logtime3 = f.getString("logtime3");
		if (!"".equals(logtime3)) {
			sql += " and logtime <= '" + logtime3 + "'";
		}
		String type = f.getString("type2");
		if (!"".equals(type)) {
			sql += " and type = '" + type + "'";
		}
		f.add("types", Jdb.get().query(super.sql_selType, CacheUtil.C0));
		String user = f.getString("user2");
		if (!"".equals(user)) {
			sql += " and user = '" + user + "'";
		}
		f.add("users", Jdb.get().query(super.sql_selUser, CacheUtil.C0));
		//
		String sql2 = " and SUBSTRING(logtime, 1, 4)='" + year
				+ "' group by SUBSTRING(logtime, 1, 7)";
		// IN
		DataMap inRes = Jdb.get().queryDataMap(sql + " and fldm='income'" + sql2);
		// OUT
		DataMap outRes = Jdb.get().queryDataMap(sql + " and fldm!='income'" + sql2);
		//
		String[] rowKeys = {"收入", "支出", "差额"};
		String[] columnKeys = new String[12];
		for(int i=1; i<=12; i++) {
			String month = i<10 ? "0"+i:""+i;
			columnKeys[i-1] = year + "-" + month;
		}
		double[][] data = new double[3][12];
		for(int j=0; j<12; j++) {
			double in = inRes.getDouble(columnKeys[j]);
			double out = outRes.getDouble(columnKeys[j]);
			data[0][j] = in;
			data[1][j] = out;
			data[2][j] = MathUtil.sub(in, out);
		}
		//System.out.println(Arrays.deepToString(data));
		//
		CategoryDataset  dataset = DatasetUtilities.createCategoryDataset(rowKeys, columnKeys, data);
		JFreeChart chart = ChartFactory.createBarChart3D("年度统计报表", "月份", "收支",
				dataset, PlotOrientation.VERTICAL, true, false, false);
		chart.getTitle().setFont(new Font("黑体", Font.PLAIN, 20));
		// 显示数字
		CategoryPlot plot = chart.getCategoryPlot();
		//plot.setForegroundAlpha(0.5F);
		//plot.setBackgroundAlpha(0.5F);
		BarRenderer3D renderer = new BarRenderer3D();
		// 设置柱子的颜色
		renderer.setSeriesPaint(0, Color.GREEN);
		renderer.setSeriesPaint(1, Color.RED);
		renderer.setSeriesPaint(2, Color.BLUE);
		//
		renderer.setBaseItemLabelGenerator(new StandardCategoryItemLabelGenerator());
		renderer.setBaseItemLabelsVisible(true);
		// 默认的数字显示在柱子中，通过如下两句可调整数字的显示
		// 注意：此句很关键，若无此句，那数字的显示会被覆盖，给人数字没有显示出来的问题
		renderer.setBasePositiveItemLabelPosition(new ItemLabelPosition(
				ItemLabelAnchor.OUTSIDE12, TextAnchor.BASELINE_LEFT));
		renderer.setItemLabelAnchorOffset(12D);
		// 设置每个地区所包含的平行柱的之间距离
		renderer.setItemMargin(0.3);
		plot.setRenderer(renderer);
		//
		String picName = DateUtil.getComplexDateTime() + ".jpeg";
		File file = new File(checkUploadPath(), picName);
		ChartUtilities.saveChartAsJPEG(file, chart, 800, 450);
		//
		//String filename = ServletUtilities.saveChartAsPNG(chart, 900, 450,	null, f.getSession());
		//f.add("reportimage", f.getRequest().getContextPath() + "/DisplayChart?filename=" + filename);		
		//
		f.add("reportimage", picName);
		f.add("columnKeys", columnKeys);
		f.add("data", data);
		f.add("year2", new Integer(year));
		//
		return m.defaultPage();
	}

	@RequestMapping("/reportmonth")
	public Page doReportmonth(Form f, Module m) throws Exception {
		String month = f.getString("month");
		String sql = "SELECT c.FLDM,c.FLMC, SUM(a.logprice) AS tp FROM ha_log a, ha_flmx b, ha_fl c WHERE a.type = b.MXDM AND b.FLDM=c.FLDM AND SUBSTRING(logtime, 1, 7)='"
				   + month + "' GROUP BY b.FLDM ORDER BY c.PX";
		String sqlm = "SELECT c.FLDM, b.MXMC, SUM(a.logprice) AS tp FROM ha_log a, ha_flmx b, ha_fl c WHERE a.type = b.MXDM AND b.FLDM=c.FLDM AND SUBSTRING(logtime, 1, 7)='" 
					+ month +"' AND c.FLDM='";
		reportMore(sql, sqlm, f);
		return m.defaultPage();
	}

	@RequestMapping("/reportyear")
	public Page doReportyear(Form f, Module m) throws Exception {
		String year = f.getString("year");
		String sql = "SELECT c.FLDM,c.FLMC, SUM(a.logprice) AS tp FROM ha_log a, ha_flmx b, ha_fl c WHERE a.type = b.MXDM AND b.FLDM=c.FLDM AND SUBSTRING(logtime, 1, 4)='"
				   + year + "' GROUP BY b.FLDM ORDER BY c.PX";
		String sqlm = "SELECT c.FLDM, b.MXMC, SUM(a.logprice) AS tp FROM ha_log a, ha_flmx b, ha_fl c WHERE a.type = b.MXDM AND b.FLDM=c.FLDM AND SUBSTRING(logtime, 1, 4)='" 
					+ year +"' AND c.FLDM='";
		reportMore(sql, sqlm, f);
		return m.defaultPage();
	}

	private void reportMore(String sql, String sqlm, Form f) throws Exception {
		List<DataMap> res = Jdb.get().query(sql);
		Map resm = new HashMap();
		//
		DefaultPieDataset dataset = new DefaultPieDataset();
		for(DataMap r : res) {
			dataset.setValue(r.getString("flmc"), r.getDouble("tp"));
			//
			String fldm = (String) r.get("fldm");
			String sqlt = sqlm + fldm + "' GROUP BY b.PX";
			resm.put(fldm, Jdb.get().query(sqlt));
		}
		JFreeChart chart = ChartFactory.createPieChart3D("收支分布图", dataset, true, true, false);
		//获得3D的水晶饼图对象
		PiePlot3D pieplot3d = (PiePlot3D) chart.getPlot();
		//设置开始角度
		pieplot3d.setStartAngle(120D);
		//设置方向为”顺时针方向“
		pieplot3d.setDirection(Rotation.CLOCKWISE);
		//设置透明度，0.5F为半透明，1为不透明，0为全透明
		pieplot3d.setForegroundAlpha(0.6F);
		pieplot3d.setNoDataMessage("无数据显示");
		//
		pieplot3d.setLabelGenerator(new StandardPieSectionLabelGenerator("{0}: ({1}元, {2})"));
		//或者采用下面自定义样式显示，{0}表示选项，{1}表示数值，{2}表示所占比例
		pieplot3d.setLegendLabelGenerator(new StandardPieSectionLabelGenerator("{0}: ({1}元, {2})"));
		//
		String picName = DateUtil.getComplexDateTime() + ".jpeg";
		File file = new File(checkUploadPath(), picName);
		ChartUtilities.saveChartAsJPEG(file, chart, 800, 450);
		//
		//String filename = ServletUtilities.saveChartAsPNG(chart, 800, 530,	null, f.getSession());
		//f.add("reportimage", f.getRequest().getContextPath() + "/DisplayChart?filename=" + filename);
		//
		f.add("reportimage", picName);
		f.add("res", res);
		f.add("resm", resm);		
	}
}
